"""
Case Type   : 全链路跟踪
Case Name   : track_stmt_stat_level设置'OFF,L0','L0,L0','L0,OFF'时不收集trace信息
Create At   : 2025-10-09
Owner       : sungang14
Description :
    1、设置guc参数gs_guc reload -N all -I all -c "enable_stmt_track=on"和gs_guc reload -N all -I all -c "log_min_duration_statement=0"
    2、建表并写入数据
    3、track_stmt_stat_level分别设置'OFF,L0','L0,L0','L0,OFF'每设置一次值后，分别执行步骤4、步骤5、步骤6和步骤7
    4、主机执行sql查询表数据
    5、在主机表statement_history中查看trace字段select trace from statement_history where query='select * from users;';
    6、select trace from dbe_perf.get_global_full_sql_by_timestamp('2020-12-01 09:25:22', '2025-12-31 23:54:41') where query='select * from users;';
    7、select trace from dbe_perf.get_statement_history('2020-12-01 09:25:22', '2025-12-31 23:54:41') where query='select * from users;';
Expect      :
    1、开启成功
    2、开启成功
    3、设置成功
    4、操作执行成功
    5、查看trace字段内容为空
    6、查看trace字段内容为空
    7、查看trace字段内容为空
History     :
"""

import os
import time
import unittest
from datetime import datetime

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()
Primary_SH = CommonSH('PrimaryDbUser')


@unittest.skipIf(3 != Primary_SH.get_node_num(), '非1+2环境不执行')
class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.comsh_std = CommonSH('Standby1DbUser')
        status = self.comsh.get_db_cluster_status('status')
        self.assertTrue(status)
        self.show_enable_stmt = 'show enable_stmt_track;'
        self.set_duration_statement = 'log_min_duration_statement=0'
        self.list_stat_level = ["track_stmt_stat_level='OFF,L0'", "track_stmt_stat_level='L0,OFF'",
                                "track_stmt_stat_level='OFF,OFF'"]
        self.reset_duration_statement = 'log_min_duration_statement=30min'
        self.table_name = f'table_trace_Case0015'
        self.prepare_data = f"drop table if exists {self.table_name};" \
                            f"CREATE TABLE {self.table_name} (id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100), birth_date DATE, balance NUMERIC(10,2) DEFAULT 0.00);" \
                            f"INSERT INTO {self.table_name} values (generate_series(1, 20),'name1', 'exa.com','2010-09-12', 4.6);"
        self.prepare_data1 = f"select * from {self.table_name};"
        self.show_track_level = 'show track_stmt_stat_level;'
        self.show_duration_statement = 'show log_min_duration_statement;'
        self.prepare_data2 = f"delete from statement_history where query like '%{self.table_name}%';"
        text = '--清理statement_history表内容--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data2, dbname='postgres')
        logger.info(data_cmd)
        self.assertIn('DELETE', data_cmd, '执行失败' + text)

    def test_slowsql(self):
        text = '--step1:开启enable_stmt_track参数;expect:开启成功--'
        logger.info(text)
        show_cmd = self.comsh.execut_db_sql(self.show_enable_stmt)
        logger.info(show_cmd)
        self.assertEquals('on', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)

        text = "--step2:设置记录每条完成语句的持续时间为0(慢sql阈值);expect:设置成功--"
        logger.info(text)
        set_cmd2 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_duration_statement)
        logger.info(set_cmd2)
        show_res = self.comsh.execut_db_sql(self.show_duration_statement)
        logger.info(show_res)
        self.assertEquals('0', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)

        text = '--step3:建表并写入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data, dbname='postgres')
        logger.info(data_cmd)
        self.assertIn(f'INSERT 0 20', data_cmd, '执行失败' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = "--step4:设置track_stmt_stat_level分别为 'OFF,OFF','L0,OFF','L0,L0'，执行表查询操作;expect:设置成功--"
        logger.info(text)
        time.sleep(3)
        current_time = datetime.now()
        start_time = current_time.strftime('%Y-%m-%d %H:%M:%S')
        logger.info(start_time)
        for set_track_level in self.list_stat_level:
            set_cmd1 = self.comsh.execute_gsguc('reload',
                                                self.constant.GSGUC_SUCCESS_MSG, set_track_level)
            logger.info(set_cmd1)
            show_res = self.comsh.execut_db_sql(self.show_track_level)
            logger.info(show_res)
            self.assertEquals(set_track_level.split('=')[-1].strip()[1:-1], show_res.splitlines()[-2].strip(),
                              '执行失败' + text)
            data_cmd = self.comsh.execut_db_sql(self.prepare_data1, dbname='postgres')
            logger.info(data_cmd)
            self.assertIn(f'(20 rows)', data_cmd, '执行失败' + text)

            text = '--step5:在主机表statement_history中查看trace字段;expect:查看trace字段内容为空--'
            logger.info(text)
            time.sleep(5)
            sql_re = self.comsh.execut_db_sql(
                f"select trace from statement_history where query='select * from {self.table_name};';",
                dbname='postgres')
            logger.info(sql_re)
            trace_msg = sql_re.splitlines()[-2].strip()
            logger.info(trace_msg)
            self.assertEqual(trace_msg, '', '执行失败' + text)

            text = '--step6:dbe_perf.get_global_full_sql_by_timestamp查询trace字段;expect:查看trace字段内容为空--'
            logger.info(text)
            current_time = datetime.now()
            end_time = current_time.strftime('%Y-%m-%d %H:%M:%S')
            logger.info(end_time)
            sql_re = self.comsh.execut_db_sql(
                f"select trace from dbe_perf.get_global_full_sql_by_timestamp('{start_time}', '{end_time}') where query='select * from {self.table_name};';",
                dbname='postgres')
            logger.info(sql_re)
            trace_msg = sql_re.splitlines()[-2].strip()
            logger.info(trace_msg)
            self.assertEqual(trace_msg, '', '执行失败' + text)

            text = '--step7:dbe_perf.get_statement_history查询trace字段;expect:查看trace字段内容为空--'
            logger.info(text)
            sql_re = self.comsh.execut_db_sql(
                f"select trace from dbe_perf.get_statement_history('{start_time}', '{end_time}') where query='select * from {self.table_name};';",
                dbname='postgres')
            logger.info(sql_re)
            trace_msg = sql_re.splitlines()[-2].strip()
            logger.info(trace_msg)
            self.assertEqual(trace_msg, '', '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        sql_re = self.comsh.execut_db_sql(f"drop table if exists {self.table_name};", dbname='postgres')
        logger.info(sql_re)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_re, '执行失败' + text)

        self.comsh.execute_gsguc('reload',
                                 self.constant.GSGUC_SUCCESS_MSG,
                                 self.list_stat_level[0])
        show_res = self.comsh.execut_db_sql(self.show_track_level)
        logger.info(show_res)
        self.assertEquals('OFF,L0', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)
        self.comsh.execute_gsguc('reload',
                                 self.constant.GSGUC_SUCCESS_MSG,
                                 self.reset_duration_statement)
        show_res = self.comsh.execut_db_sql(self.show_duration_statement)
        logger.info(show_res)
        self.assertEquals('30min', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
